﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Region.Utils;
using MySql.Data.MySqlClient;
namespace Region
{
  [DBObject(Table = "kont", PrimaryKeyPole = "KontID")]
  public class Kont : DBObject
  {
      protected Groupe m_Groupe;       
      public struct KontObject
      {
          //public Int32 KontObjectID;
          public Int32 KontObjectDirect;
          public Int32 KontObjectZPP;
          public Int32 KontObjectRaionID;
          public String KontObjectName;
          public String KontObjectTelefon;
          public String KontObjectRegion;
          public String KontObjectGorod;
          public String KontObjectNaspunkt;
          public String KontObjectStreet;
          public String KontObjectDom;
          public String KontObjectKorpus;
          public String KontObjectKvart;
          public String KontObjectPostIndex;
          public String KontObjectOKVED;
      }
      protected Dictionary<Int32, bool> d_KontObjectsEdit;
      protected Dictionary<Int32, KontObject> d_KontObjects;
      protected bool UseRaspor;
      protected bool UsePlan;
      protected Dictionary<Int32, bool> UseObjectRaspor;
      protected Dictionary<Int32, bool> UseObjectPlan;
      public Dictionary<Int32, KontObject> KontObjects { get { return new Dictionary<Int32, KontObject>(d_KontObjects); } }
      [DBColumn] public Int32 KontID { get; set; }
      [DBColumn] public Int32 KontForm { get; set; }
      [DBColumn] public Int32 KontTypeID { get; set; }
      [DBColumn(PoleType = EPoleType.RegionDate)]
      public RegionDate KontStart { get; set; }
      [DBColumn(PoleType = EPoleType.RegionDate)]
      public RegionDate KontGosReg { get; set; }
      [DBColumn(PoleType = EPoleType.RegionDate)]
      public RegionDate KontDelDate { get; set; }
      [DBColumn(PoleType = EPoleType.RegionDate)]
      public RegionDate KontOldCheck { get; set; }
      [DBColumn] public String KontName { get; set; }
      [DBColumn] public String KontBosName { get; set; }
      [DBColumn] public String KontTelefon { get; set; }
      [DBColumn] public String KontInfo { get; set; }
      [DBColumn] public String INN { get; set; }
      [DBColumn] public String OGRN { get; set; }
      [DBColumn] public String KontPostIndex { get; set; }
      [DBColumn] public String KontRegion { get; set; }
      [DBColumn] public String KontRaion { get; set; }
      [DBColumn] public String KontGorod { get; set; }
      [DBColumn] public String KontNaspunkt { get; set; }
      [DBColumn] public String KontStreet { get; set; }
      [DBColumn] public String KontDom { get; set; }
      [DBColumn] public String KontKorpus { get; set; }
      [DBColumn] public String KontKvart { get; set; }

      [DBColumn] public String KontFPostIndex { get; set; }
      [DBColumn] public String KontFRegion { get; set; }
      [DBColumn] public String KontFRaion { get; set; }
      [DBColumn] public String KontFGorod { get; set; }
      [DBColumn] public String KontFNaspunkt { get; set; }
      [DBColumn] public String KontFStreet { get; set; }
      [DBColumn] public String KontFDom { get; set; }
      [DBColumn] public String KontFKorpus { get; set; }
      [DBColumn] public String KontFKvart { get; set; }
      
      public String KontULAddress { get { return Engine.getAddressString(KontPostIndex, KontRegion, KontRaion, KontGorod, KontNaspunkt, KontStreet, KontDom, KontKorpus, KontKorpus); } }
      public String KontFKAddress { get { return Engine.getAddressString("", KontFRegion, KontFRaion, KontFGorod, KontFNaspunkt, KontFStreet, KontFDom, KontFKorpus, KontFKorpus); } }
      public Boolean isIP(){return INN.Length == 12 && OGRN.Length==15;}
      
      public static bool checkINN(String INN)
      {
          try
          {
              Dictionary<char, Int32> conv = new Dictionary<char, Int32>() { { '0', 0 }, { '1', 1 }, { '2', 2 }, { '3', 3 }, { '4', 4 }, { '5', 5 }, { '6', 6 }, { '7', 7 }, { '8', 8 }, { '9', 9 } };
              if (INN.Length == 10)
              {
                  Int32[] A = new Int32[10];
                  for (int i = 0; i < 10; i++) A[i] = conv[INN[i]];
                  Int32 Check10 = ((2 * A[0] + 4 * A[1] + 10 * A[2] + 3 * A[3] + 5 * A[4] + 9 * A[5] + 4 * A[6] + 6 * A[7] + 8 * A[8]) % 11) % 10;
                  return A[9] == Check10;
              }
              if (INN.Length == 12)
              {
                  Int32[] A = new Int32[12];
                  for (int i = 0; i < 12; i++) A[i] = conv[INN[i]];
                  Int32 Check11 = ((7 * A[0] + 2 * A[1] + 4 * A[2] + 10 * A[3] + 3 * A[4] + 5 * A[5] + 9 * A[6] + 4 * A[7] + 6 * A[8] + 8 * A[9]) % 11) % 10;
                  Int32 Check12 = ((3 * A[0] + 7 * A[1] + 2 * A[2] + 4 * A[3] + 10 * A[4] + 3 * A[5] + 5 * A[6] + 9 * A[7] + 4 * A[8] + 6 * A[9] + 8 * A[10]) % 11) % 10;
                  return A[10] == Check11 && A[11] == Check12;
              }
              else return false;
          }
          catch { return false; }
      }
      public static bool checkOGRN(String OGRN)
      {
          try
          {
              Dictionary<char, Int32> conv = new Dictionary<char, Int32>() { { '0', 0 }, { '1', 1 }, { '2', 2 }, { '3', 3 }, { '4', 4 }, { '5', 5 }, { '6', 6 }, { '7', 7 }, { '8', 8 }, { '9', 9 } };
              if (OGRN.Length == 13)
              {
                  Int64 V = 0;
                  for (int i = 0; i < 12; i++) V = V * 10 + conv[OGRN[i]];
                  Int64 Check13 = (V % 11) % 10;
                  return conv[OGRN[12]] == Check13;
              }
              if (OGRN.Length == 15)
              {
                  Int64 V = 0;
                  for (int i = 0; i < 14; i++) V = V * 10 + conv[OGRN[i]];
                  Int64 Check15 = (V % 13) % 10;
                  return conv[OGRN[14]] == Check15;
              }
              else return false;
          }
          catch { return false; }
      }
      public override bool read(MySqlConnection RegionDB=null)
      {
          d_KontObjects = new Dictionary<Int32, KontObject>();

          //d_KontPlanID = new Dictionary<Int32, Int32>();
          if (!base.read(RegionDB)) return false;
          SQLResult r1 = m_engine.SQLSelect("SELECT * FROM kontobject WHERE KontID=@KontID", new SQLParam() { { "@KontID", PKValue } },RegionDB);
          try
          {
              for (Int32 i = 0; i < r1.Count; i++)
              {
                  KontObject KO = new KontObject();
                  Int32 KontObjectID = (Int32)r1[i]["KontObjectID"];
                  //KO.KontObjectID = (Int32)r1[i]["KontObjectID"];
                  KO.KontObjectDirect = (Int32)r1[i]["KontObjectDirect"];
                  KO.KontObjectZPP = (Int32)r1[i]["KontObjectZPP"];
                  KO.KontObjectRaionID = (Int32)r1[i]["KontObjectRaionID"];
                  KO.KontObjectName = Engine.RegionStringToString((String)r1[i]["KontObjectName"]);
                  KO.KontObjectTelefon = Engine.RegionStringToString((String)r1[i]["KontObjectTelefon"]);
                  KO.KontObjectRegion = Engine.RegionStringToString((String)r1[i]["KontObjectRegion"]);
                  KO.KontObjectGorod = Engine.RegionStringToString((String)r1[i]["KontObjectGorod"]);
                  KO.KontObjectNaspunkt = Engine.RegionStringToString((String)r1[i]["KontObjectNaspunkt"]);
                  KO.KontObjectStreet = Engine.RegionStringToString((String)r1[i]["KontObjectStreet"]);
                  KO.KontObjectDom = Engine.RegionStringToString((String)r1[i]["KontObjectDom"]);
                  KO.KontObjectKorpus = Engine.RegionStringToString((String)r1[i]["KontObjectKorpus"]);
                  KO.KontObjectKvart = Engine.RegionStringToString((String)r1[i]["KontObjectKvart"]);
                  KO.KontObjectPostIndex = Engine.RegionStringToString((String)r1[i]["KontObjectPostIndex"]);
                  KO.KontObjectOKVED = Engine.RegionStringToString((String)r1[i]["KontObjectOKVED"]);
                  d_KontObjects[KontObjectID] = KO;
              }
          }
          catch { return false; }
          // 
          SQLResult r2 = m_engine.SQLSelect("SELECT RasporID FROM raspor WHERE (RucPodpis>0 OR NachPodpis>0 OR StatusVip>0) AND KontID=@KontID LIMIT 1", new SQLParam() { { "@KontID", PKValue } },RegionDB);
          UseRaspor = (r2.Count > 0);
          //
          SQLResult r3 = m_engine.SQLSelect("SELECT PlanID FROM plan WHERE RegNum>0 AND KontID=@KontID", new SQLParam() { { "@KontID", PKValue } },RegionDB);
          UsePlan = (r3.Count > 0);
          //
          UseObjectRaspor = new Dictionary<Int32, bool>();
          SQLResult r4 = m_engine.SQLSelect("SELECT kontobject.KontObjectID FROM kont INNER JOIN raspor ON raspor.KontID=kont.KontID INNER JOIN rasporobject ON rasporobject.RasporID=raspor.RasporID INNER JOIN kontobject ON kontobject.KontObjectID=rasporobject.KontObjectID WHERE kont.KontID = @KontID AND (RucPodpis<>0 OR NachPodpis<>0 OR StatusVip=1) GROUP BY kontobject.KontObjectID", new SQLParam() { { "@KontID", PKValue } },RegionDB);
          for (Int32 i = 0; i < r4.Count; i++) UseObjectRaspor[(Int32)r4[i]["KontObjectID"]] = true;
          //
          UseObjectPlan = new Dictionary<Int32, bool>();
          SQLResult r5 = m_engine.SQLSelect("SELECT kontobject.KontObjectID FROM kont INNER JOIN plan ON plan.KontID=kont.KontID INNER JOIN planobject ON planobject.PlanID=plan.PlanID INNER JOIN kontobject ON kontobject.KontObjectID=planobject.KontObjectID WHERE kont.KontID = 1198  AND (RegNum>0) GROUP BY kontobject.KontObjectID", new SQLParam() { { "@KontID", PKValue } },RegionDB);
          for (Int32 i = 0; i < r5.Count; i++) UseObjectPlan[(Int32)r5[i]["KontObjectID"]] = true;
          return true;
      }
      public Int32 getKontIDfromOGRN(String OGRN)
      {
        return 0;
      }
      public bool Save()
      {
          {//Объекты
              foreach (KeyValuePair<Int32, bool> KOE in d_KontObjectsEdit)
                  if (KOE.Value)
                  {
                      m_engine.SQLEdit(
                      @"
UPDATE kontobject 
SET KontObjectDirect=@KontObjectDirect,KontObjectZPP=@KontObjectZPP,
KontObjectRaionID=@KontObjectRaionID,KontObjectName=@KontObjectName,
KontObjectTelefon=@KontObjectTelefon,KontObjectRegion=@KontObjectRegion,
KontObjectGorod=@KontObjectGorod,KontObjectNaspunkt=@KontObjectNaspunkt,
KontObjectStreet=@KontObjectStreet,KontObjectDom=@KontObjectDom,
KontObjectKorpus=@KontObjectKorpus,KontObjectKvart=@KontObjectKvart,
KontObjectPostIndex=@KontObjectPostIndex,KontObjectOKVED=@KontObjectOKVED
WHERE KontObjectID=@KontObjectID",
                      new SQLParam() { 
                  { "@KontObjectID", KOE.Key }, 
                  { "@KontObjectDirect", d_KontObjects[KOE.Key].KontObjectDirect }, 
                  { "@KontObjectZPP", d_KontObjects[KOE.Key].KontObjectZPP }, 
                  { "@KontObjectRaionID", d_KontObjects[KOE.Key].KontObjectRaionID },
                  { "@KontObjectName", d_KontObjects[KOE.Key].KontObjectName },
                  { "@KontObjectTelefon", d_KontObjects[KOE.Key].KontObjectTelefon },
                  { "@KontObjectRegion", d_KontObjects[KOE.Key].KontObjectRegion },
                  { "@KontObjectGorod", d_KontObjects[KOE.Key].KontObjectGorod },
                  { "@KontObjectNaspunkt", d_KontObjects[KOE.Key].KontObjectNaspunkt },
                  { "@KontObjectStreet", d_KontObjects[KOE.Key].KontObjectStreet },
                  { "@KontObjectDom", d_KontObjects[KOE.Key].KontObjectDom },
                  { "@KontObjectKorpus", d_KontObjects[KOE.Key].KontObjectKorpus },
                  { "@KontObjectKvart", d_KontObjects[KOE.Key].KontObjectKvart },
                  { "@KontObjectPostIndex", d_KontObjects[KOE.Key].KontObjectPostIndex },
                  { "@KontObjectOKVED", d_KontObjects[KOE.Key].KontObjectOKVED } 
                  });
                  }
          }
          return base.save();
      }
      public List<String> checkKont()
      {
          List<String> L = new List<String>();
          if (!checkINN(INN)) L.Add("Контрагент: Ошибка в ИНН");
          if (!checkOGRN(OGRN)) L.Add("Контрагент: Ошибка в ОГРН");
          if (KontTypeID == 0) L.Add("Контрагент: Неуказан тип предприятия");
          if (KontForm == 0) L.Add("Контрагент: Неуказан форма предприятия");
          if (KontName.Trim() == "") L.Add("Контрагент: Ошибка наименование контрагента неможет быть пустым");
          if (KontULAddress.Trim() == "") L.Add("Контрагент: Ошибка у контрагента не заполнен юридический адрес");
          else
              if (KontRegion.Trim() == "") L.Add("Контрагент: В юридическом адресе не заполнен регио");
          foreach (KeyValuePair<Int32, KontObject> IO in d_KontObjects)
          {
              String ObjectName = IO.Value.KontObjectName;
              if (IO.Value.KontObjectName.Trim() == "") { L.Add("Контрагент: У одного из объектов неуказано наименование"); ObjectName = "[ОБЪЕКТ БЕЗ НАИМЕНОВАНИЯ]"; }
              if (IO.Value.KontObjectDirect == 0) L.Add("Контрагент: У объекта " + ObjectName + " неуказано направление деятельности");
              if (IO.Value.KontObjectRaionID == 0) L.Add("Контрагент: У объекта " + ObjectName + " неуказан район");
              if (IO.Value.KontObjectRegion.Trim() == "") L.Add("Контрагент: У объекта " + ObjectName + " неуказан регион");
              if (IO.Value.KontObjectOKVED == "") L.Add("Контрагент: У объекта " + ObjectName + " неуказан ОКВЭД");
          }
          return L;
      }
      static Dictionary<String,Boolean> PDnPoles=new Dictionary<String,Boolean>{{"KontTelefon",true},{"KontPostIndex",true},{"KontRegion",true},{"KontRaion",true},{"KontGorod",true},{"KontNaspunkt",true},{"KontStreet",true},{"KontDom",true},{"KontKorpus",true},{"KontKvart",true},{"KontFPostIndex",true},{"KontFRegion",true},{"KontFRaion",true},{"KontFGorod",true},{"KontFNaspunkt",true},{"KontFStreet",true},{"KontFDom",true},{"KontFKorpus",true},{"KontFKvart",true}};
      public override bool poleAccess(String PoleName)
      {
          //return false;
          if (isIP() && PDnPoles.ContainsKey(PoleName) && PDnPoles[PoleName]) return false;// нельзя редактирование подля с персональными данными
          if (m_engine.isAccess("Admin")) return true;//Ктож ему запретит на то он и Одмин
          if (UseRaspor) return false;//Нельзя редактировать такак есть выписанные распоряжения
          if (UsePlan) return false;//Нельзя редактировать такак субъект стоит в плане
          
          return true;
      }
      public bool kontObjectAccess(Int32 KontObjectID) { return !UseObjectRaspor.ContainsKey(KontObjectID) || !UseObjectPlan.ContainsKey(KontObjectID); }
      public bool setKontObject(Int32 KontObjectID, KontObject Object)
      {
          if (!kontObjectAccess(KontObjectID)) return false;
          if (!d_KontObjects.ContainsKey(KontObjectID)) return false;
          d_KontObjects[KontObjectID] = Object;
          d_KontObjectsEdit[KontObjectID] = true;
          return true;
      }
      public Kont(Engine engine, Int32 KontID)
          : base(engine,  KontID)
      {
          d_KontObjects = new Dictionary<Int32, KontObject>();
          d_KontObjectsEdit = new Dictionary<Int32, bool>();
          //read();
      }
      /*public override void showEditor() 
      {
          KontForm F = new KontForm(m_engine,KontID);
          F.ShowDialog();
      }*/
  }
  public struct KontListLine
  {
    public Int32 KontID;
    public String INN;
    public String OGRN;
    public String KontName;
    public Boolean My;
  }
  public class KontList:DBList<KontListLine>
  {
    public bool ExportListXMLX(String FileName)
    {
      var EL = new List<Dictionary<String,Object>>();
      var Raions = m_engine.Raions;
      var KT = m_engine.KontType;
      //var Osnov = m_engine.Osnov;
      //var Otdels = m_engine.Otdels;
      setWhere("IgnorePage", true);
      var old_lines = new List<KontListLine>(m_lines);
      Filter();
      //var YNN_List = new Dictionary<Int32,String>(){{0,"Нет"},{1,"Да"},{-1,"[Не указан]"}};
      //var NNar_List = new Dictionary<Int32,String>(){{0,"Выявлены"},{1,"Не выявлены"},{-1,"[Не указано]"}};
      foreach(KontListLine line in m_lines)
      {
        var ExportLine = new Dictionary<String,Object>();
        var Kont = m_engine.getKont(line.KontID);
        ExportLine["%KontName%"] = Kont.KontName;
        ExportLine["%INN%"] = Kont.INN;
        ExportLine["%OGRN%"] = Kont.OGRN;
        ExportLine["%KontType%"] = KT[Kont.KontTypeID];
        
        ExportLine["%KontPostIndex%"] = Kont.KontPostIndex;
        ExportLine["%KontRegion%"] = Kont.KontRegion;
        ExportLine["%KontRaion%"] = Kont.KontRaion;
        ExportLine["%KontGorod%"] = Kont.KontGorod;
        ExportLine["%KontNaspunkt%"] = Kont.KontNaspunkt;
        ExportLine["%KontStreet%"] = Kont.KontStreet;
        ExportLine["%KontDom%"] = Kont.KontDom;
        ExportLine["%KontKorpus%"] = Kont.KontKorpus;
        ExportLine["%KontKvart%"] = Kont.KontKvart;
        
        ExportLine["%KontFPostIndex%"] = Kont.KontFPostIndex;
        ExportLine["%KontFRegion%"] = Kont.KontFRegion;
        ExportLine["%KontFRaion%"] = Kont.KontFRaion;
        ExportLine["%KontFGorod%"] = Kont.KontFGorod;
        ExportLine["%KontFNaspunkt%"] = Kont.KontFNaspunkt;
        ExportLine["%KontFStreet%"] = Kont.KontFStreet;
        ExportLine["%KontFDom%"] = Kont.KontFDom;
        ExportLine["%KontFKorpus%"] = Kont.KontFKorpus;
        ExportLine["%KontFKvart%"] = Kont.KontFKvart;

        ExportLine["%KontULAddress%"] = Kont.KontULAddress;
        ExportLine["%KontFKAddress%"] = Kont.KontFKAddress;
        ExportLine["%KontGosReg%"] = Kont.KontGosReg;
        ExportLine["%ObjectList%"] = "";
        
        foreach(var KontObjectItem in Kont.KontObjects)          
        {                            
          var KontObject = KontObjectItem.Value;
          ExportLine["%ObjectList%"]+=  KontObject.KontObjectName + ", " + Engine.getAddressString(KontObject.KontObjectPostIndex,KontObject.KontObjectRegion,Raions[KontObject.KontObjectRaionID],KontObject.KontObjectGorod,KontObject.KontObjectNaspunkt,KontObject.KontObjectStreet,KontObject.KontObjectDom,KontObject.KontObjectKorpus,KontObject.KontObjectKvart)+";\n";
        }           
        EL.Add(ExportLine);
      }      
      setWhere("IgnorePage", false);
      m_lines=old_lines;
      var Template = m_engine.getRegionFile("ReestrKont","xlsx");
      var Poles = new Dictionary<String,Object>();
      var Tables = new Dictionary<String,List<Dictionary<String,Object>>>(){{"%KontName%",EL}};
      Export.export("xlsx",Template,FileName,Poles,Tables);
      return true;
    }
    public KontList(Engine engine): base(engine)
    {
      DefaultPoles.Add("kont.KontID");
      DefaultPoles.Add("kont.INN");
      DefaultPoles.Add("kont.OGRN");
      DefaultPoles.Add("kont.KontName");
      
      DefaultTables.Add("kont");
      DefaultOrderBy.Add("kont.KontName");
    }
    protected override void ProceessWheres(SQLParam Params,List<String> Tables, List<String> Poles,List<String> Wheres,List<String> OrderBy)
    {
      Poles.Add("KontID IN (SELECT KontID FROM userkont WHERE UserID=@MyUserID) AS My");
      Params["@MyUserID"] = m_engine.CurrentUser.UserID;
      if (m_wheres.ContainsKey("INN") && m_wheres["INN"] != null)
      {
        Wheres.Add("kont.INN=@INN");
        Params["@INN"] = (String)m_wheres["INN"];
      }
      if (m_wheres.ContainsKey("OGRN") && m_wheres["OGRN"] != null)
      {
        Wheres.Add("kont.OGRN=@OGRN");
        Params["@OGRN"] = (String)m_wheres["OGRN"];
      }
      if (m_wheres.ContainsKey("OGRN_LENGTH") && m_wheres["OGRN_LENGTH"] != null)
      {
        Wheres.Add("CHAR_LENGTH(kont.OGRN)=@OGRN_LENGTH");
        Params["@OGRN_LENGTH"] = (Int32)m_wheres["OGRN_LENGTH"];
      }
      if (m_wheres.ContainsKey("Find") && m_wheres["Find"] != null)
      {
        String Find = ((String)m_wheres["Find"]).Trim();
        if (Find.Length > 3)
        {
          //Poles.Add("(MATCH(KontName,KontGorod,KontNaspunkt,KontStreet,KontStreet,KontFGorod,KontFNaspunkt,KontFStreet,INN,OGRN) AGAINST ('эльбан')) AS M1");
          Wheres.Add("(MATCH(KontName,KontGorod,KontNaspunkt,KontStreet,KontStreet,KontFGorod,KontFNaspunkt,KontFStreet,INN,OGRN) AGAINST (@Find1 IN BOOLEAN MODE))");
          OrderBy.Add("(MATCH(KontName,KontGorod,KontNaspunkt,KontStreet,KontStreet,KontFGorod,KontFNaspunkt,KontFStreet,INN,OGRN) AGAINST (@Find2)) DESC");
          Params["@Find1"] = (String)m_wheres["Find"];
          Params["@Find2"] = (String)m_wheres["Find"];
        }
        else if (Find.Length > 0)
        {
          Wheres.Add("KontName LIKE @Find1");
          Params["@Find1"] = "%" + (String)m_wheres["Find"] + "%";
        }
      }      
    }
    protected override KontListLine readLine(Dictionary<String, Object> r)
    {
      var l = new KontListLine();
      l.KontID = (Int32)r["KontID"];
      l.INN = Engine.RegionStringToString((String)r["INN"]);
      l.OGRN = Engine.RegionStringToString((String)r["OGRN"]);
      l.KontName = Engine.RegionStringToString((String)r["KontName"]);
      if (l.INN=="") l.INN = "[Не укзан]";
      if (l.OGRN=="") l.OGRN = "[Не укзан]";
      if (l.KontName=="") l.KontName = "[Не укзан]";
      l.My = (Boolean)((Int64)r["My"] != 0);
      return l;
    }
    
    public struct EGRLine
    {
      public String KontName;
      public String INN;
      public String OGRN;
      public DateTime GosReg;
      public String Region;
      public String Raion;
      public String Gorod;
      public String Naspunkt;
      public String Street;
      public String Dom;
      public String Korpus;
      public String Office;
      public override string ToString()
      {
        return OGRN;
      }
    }
    public static Int32 ImportEGR(Engine engine, EGRLine line)
    {
        //MySqlDataReader r;
        if (line.KontName.Trim() == "") return 0;//ошибка
        if (!Kont.checkINN(line.INN)) return 0;//Ошибка в ИНН
        if (!Kont.checkOGRN(line.OGRN)) return 0;//Ошибка в ОГРН
        // Проверить есть ли такой контрагент вреестре
        SQLResult r1 = engine.SQLSelect("SELECT KontID FROM kont WHERE INN=@INN AND OGRN=@OGRN", new SQLParam() { { "@INN", line.INN }, { "@OGRN", line.OGRN } });
        if (r1.Count > 0) return (Int32)r1[0]["KontID"];
        //Вставка контрагента
        SQLParam P = new SQLParam();
        P["@INN"] = Engine.StringToRegionString(line.INN);
        P["@OGRN"] = Engine.StringToRegionString(line.OGRN);
        P["@KontName"] = Engine.StringToRegionString(line.KontName);
        P["@KontGosReg"] = Engine.RegionDateToInt(line.GosReg);
        P["@KontRegion"] = Engine.StringToRegionString(line.Region);
        P["@KontRaion"] = Engine.StringToRegionString(line.Raion);
        P["@KontGorod"] = Engine.StringToRegionString(line.Gorod);
        P["@KontNaspunkt"] = Engine.StringToRegionString(line.Naspunkt);
        P["@KontStreet"] = Engine.StringToRegionString(line.Street);
        P["@KontDom"] = Engine.StringToRegionString(line.Dom);
        P["@KontKorpus"] = Engine.StringToRegionString(line.Korpus);
        P["@KontKvart"] = Engine.StringToRegionString(line.Office);
        engine.SQLEdit("INSERT INTO kont(INN,OGRN,KontName,KontGosReg,KontRegion,KontRaion,KontGorod,KontNaspunkt,KontStreet,KontDom,KontKorpus,KontKvart) VALUES (@INN,@OGRN,@KontName,@KontGosReg,@KontRegion,@KontRaion,@KontGorod,@KontNaspunkt,@KontStreet,@KontDom,@KontKorpus,@KontKvart)", P);

        //engine.SQLEdit("INSERT INTO kont(INN,OGRN,KontName,KontGosReg,KontRegion,KontRaion,KontGorod,KontNaspunkt,KontStreet,KontDom,KontKorpus,KontKvart) VALUES (@INN,@OGRN,@KontName,@KontGosReg,@KontRegion,@KontRaion,@KontGorod,@KontNaspunkt,@KontStreet,@KontDom,@KontKorpus,@KontKvart)", new SQLParam() { { "@INN", line.INN }, { "@OGRN", line.OGRN } });

        // Проверить вставился ли контрагент в реестр
        SQLResult r2 = engine.SQLSelect("SELECT KontID FROM kont WHERE INN=@INN AND OGRN=@OGRN", new SQLParam() { { "@INN", line.INN }, { "@OGRN", line.OGRN } });
        if (r2.Count > 0) return (Int32)r2[0]["KontID"];
        return 0;//Вставка неудалась
    }
    static public List<EGRLine> getListEGRUL(Engine engine,Dictionary<String, Object> Where)
    {
        List<EGRLine> EGRUL = new List<EGRLine>();
       
        SqlConnection con = new SqlConnection(engine.config.getValue<String>("EGRIPBD",""));
        Dictionary<String, Object> P = new Dictionary<String, Object>();
        List<String> OrderBy = new List<String>();
        List<String> W = new List<String>();
        //try
        {
            if (Where.ContainsKey("INN"))
            {
                String INN = Where["INN"].ToString();
                if (INN.Length == 10)
                {
                    W.Add("INN = @INN");
                    P["@INN"] = INN;
                }
                else if (INN.Length < 10)
                {
                    W.Add("INN LIKE @INN");
                    P["@INN"] = "%" + INN + "%";
                }
            }
            if (Where.ContainsKey("OGRN"))
            {
                String OGRN = Where["OGRN"].ToString();
                if (OGRN.Length == 13)
                {
                    W.Add("OGRN = @OGRN");
                    P["@OGRN"] = OGRN;
                }
                else if (OGRN.Length < 13)
                {
                    W.Add("OGRN LIKE @OGRN");
                    P["@OGRN"] = "%" + OGRN + "%";
                }
            }
            if (Where.ContainsKey("FindAddress") && Where.ContainsKey("FindName"))
            {
                String FindName = Where["FindName"].ToString();
                String FindAddress = Where["FindAddress"].ToString();
                Find F1 = new Find(FindName);
                Find F2 = new Find(FindAddress);
                String S1 = "";
                String S2 = "";
                F1.forMSSQLOrderBy("UL_NAME.NAMEP", out S1, ref P);
                F2.forMSSQLOrderBy("UL_GOROD.NAME+UL_STREET.NAME+DOM", out S2, ref P);
                OrderBy.Add(S1 + "+" + S2 + " DESC");
                F1.forMSSQLWhere("UL_NAME.NAMEP", out S1, ref P);
                F2.forMSSQLWhere("UL_GOROD.NAME+UL_STREET.NAME+DOM", out S2, ref P);
                W.Add("(" + S1 + ") AND (" + S2 + ")");
            }
            else if (Where.ContainsKey("FindName"))
            {

                String FindName = Where["FindName"].ToString();
                Find F = new Find(FindName);
                //W.Add("UL_NAME.NAMEP LIKE @Name");
                //P["@Name"] = "%" + Name + "%";
                String S = "";
                F.forMSSQLOrderBy("UL_NAME.NAMEP", out S, ref P);
                OrderBy.Add(S + " DESC");
                F.forMSSQLWhere("UL_NAME.NAMEP", out S, ref P);
                W.Add(S);
            }
            else if (Where.ContainsKey("FindAddress"))
            {

                String FindAddress = Where["FindAddress"].ToString();
                Find F = new Find(FindAddress);
                //W.Add("UL_NAME.NAMEP LIKE @Name");
                //P["@Name"] = "%" + Name + "%";
                String S = "";
                F.forMSSQLOrderBy("UL_GOROD.NAME+UL_STREET.NAME+DOM", out S, ref P);
                OrderBy.Add(S + " DESC");
                F.forMSSQLWhere("UL_GOROD.NAME+UL_STREET.NAME+DOM", out S, ref P);
                W.Add(S);
            }
            if (Where.ContainsKey("Name"))
            {
                String Name = Where["Name"].ToString();
                W.Add("UL_NAME.NAMEP LIKE @Name");
                P["@Name"] = "%" + Name + "%";
            }
            if (Where.ContainsKey("Gorod"))
            {
                String Gorod = Where["Gorod"].ToString();
                W.Add("UL_GOROD.NAME LIKE @Gorod");
                P["@Gorod"] = "%" + Gorod + "%";
            }
            if (Where.ContainsKey("Street"))
            {
                String Street = Where["Street"].ToString();
                W.Add("UL_STREET.NAME LIKE @Street");
                P["@Street"] = "%" + Street + "%";
            }
            if (Where.ContainsKey("Dom"))
            {
                String Dom = Where["Dom"].ToString();
                W.Add("DOM LIKE @Dom");
                P["@Dom"] = "%" + Dom + "%";
            }
            con.Open();
            EGRLine E = new EGRLine();
            String WhereSQL = "";
            if (W.Count > 0) WhereSQL = " WHERE (" + String.Join(") AND (", W.ToArray()) + ")";
            String OrderBySQL = "";
            if (OrderBy.Count > 0) OrderBySQL = " ORDER BY " + String.Join(", ", OrderBy.ToArray());
            String SQL = @"
SELECT TOP 5 NAMEP,INN,OGRN,UL_REGION.NAME AS REGION, UL_RAION.NAME AS RAION, UL_GOROD.NAME AS GOROD, UL_NASPUNKT.NAME AS NASPUNKT,UL_STREET.NAME AS STREET,DOM,KORP,KVART,
DAY(UL_START.DTREG)AS Day, MONTH(UL_START.DTREG)AS Month,YEAR(UL_START.DTREG) AS Year
FROM UL_UL
INNER JOIN UL_NAME ON UL_NAME.IDUL=UL_UL.IDUL
LEFT JOIN UL_UL_ADDRESS ON UL_UL_ADDRESS.IDUL=UL_UL.IDUL
LEFT JOIN UL_ADDRESS ON UL_ADDRESS.IDADDRESS=UL_UL_ADDRESS.IDADDRESS
LEFT JOIN UL_REGION ON UL_REGION.IDREGION=UL_ADDRESS.IDREGION
LEFT JOIN UL_RAION ON UL_RAION.IDRAION=UL_ADDRESS.IDRAION
LEFT JOIN UL_GOROD ON UL_GOROD.IDGOROD=UL_ADDRESS.IDGOROD
LEFT JOIN UL_NASPUNKT ON UL_NASPUNKT.IDNASPUNKT=UL_ADDRESS.IDNASPUNKT
LEFT JOIN UL_STREET ON UL_STREET.IDSTREET=UL_ADDRESS.IDSTREET
LEFT JOIN UL_CONTACT ON UL_CONTACT.IDCONTACT=UL_UL_ADDRESS.IDCONTACT
LEFT JOIN UL_START ON UL_START.IDUL=UL_UL.IDUL  
" + WhereSQL + OrderBySQL;
            SqlCommand cmd = new SqlCommand(SQL, con);
            foreach (KeyValuePair<String, Object> V in P) cmd.Parameters.AddWithValue(V.Key, V.Value);
            SqlDataReader r = cmd.ExecuteReader();
            //try                
            {
                Dictionary<String, Int32> Pole = new Dictionary<String, Int32>();
                for (Int32 i = 0; i < r.FieldCount; i++) Pole[r.GetName(i)] = i;
                while (r.Read())
                {
                    E.KontName = r.GetString(Pole["NAMEP"]);
                    E.INN = r.GetString(Pole["INN"]);
                    E.OGRN = r.GetString(Pole["OGRN"]);
                    if (!r.IsDBNull(Pole["REGION"])) E.Region = r.GetString(Pole["REGION"]); else E.Region = "";
                    if (!r.IsDBNull(Pole["RAION"])) E.Raion = r.GetString(Pole["RAION"]); else E.Raion = "";
                    if (!r.IsDBNull(Pole["GOROD"])) E.Gorod = r.GetString(Pole["GOROD"]); else E.Gorod = "";
                    if (!r.IsDBNull(Pole["NASPUNKT"])) E.Naspunkt = r.GetString(Pole["NASPUNKT"]); else E.Naspunkt = "";
                    if (!r.IsDBNull(Pole["STREET"])) E.Street = r.GetString(Pole["STREET"]); else E.Street = "";
                    if (!r.IsDBNull(Pole["DOM"])) E.Dom = r.GetString(Pole["DOM"]); else E.Dom = "";

                    if (!r.IsDBNull(Pole["KORP"])) E.Korpus = r.GetString(Pole["KORP"]); else E.Korpus = "";
                    if (!r.IsDBNull(Pole["KVART"])) E.Office = r.GetString(Pole["KVART"]); else E.Office = "";
                    if (!r.IsDBNull(Pole["Year"]) && !r.IsDBNull(Pole["Month"]) && !r.IsDBNull(Pole["Day"])) E.GosReg = new DateTime(r.GetInt32(Pole["Year"]), r.GetInt32(Pole["Month"]), r.GetInt32(Pole["Day"]));
                    EGRUL.Add(E);
                }
            }
            //finally
            { r.Close(); }
        }
        //catch{}
        //finally 
        { con.Close(); }
        return EGRUL;
    }
    static public List<EGRLine> getListEGRIP(Engine engine,Dictionary<String, Object> Where)
    {
        List<EGRLine> EGRUL = new List<EGRLine>();
        SqlConnection con = new SqlConnection(engine.config.getValue<String>("EGRIPBD",""));
        Dictionary<String, Object> P = new Dictionary<String, Object>();
        List<String> OrderBy = new List<String>();
        List<String> W = new List<String>();
        //try
        {
            if (Where.ContainsKey("INN"))
            {
                String INN = Where["INN"].ToString();
                if (INN.Length == 10)
                {
                    W.Add("INN = @INN");
                    P["@INN"] = INN;
                }
                else if (INN.Length < 10)
                {
                    W.Add("INN LIKE @INN");
                    P["@INN"] = "%" + INN + "%";
                }
            }
            if (Where.ContainsKey("OGRN"))
            {
                String OGRN = Where["OGRN"].ToString();
                if (OGRN.Length == 10)
                {
                    W.Add("OGRN = @OGRN");
                    P["@OGRN"] = OGRN;
                }
                else if (OGRN.Length < 10)
                {
                    W.Add("OGRN LIKE @OGRN");
                    P["@OGRN"] = "%" + OGRN + "%";
                }
            }
            if (Where.ContainsKey("FindName"))
            {

                String FindName = Where["FindName"].ToString();
                Find F = new Find(FindName);
                //W.Add("UL_NAME.NAMEP LIKE @Name");
                //P["@Name"] = "%" + Name + "%";
                String S = "";
                F.forMSSQLOrderBy("FL_FL.FAM_FL+' '+FL_FL.NAME_FL+' '+FL_FL.OTCH_FL", out S, ref P);
                OrderBy.Add(S + " DESC");
                F.forMSSQLWhere("FL_FL.FAM_FL+' '+FL_FL.NAME_FL+' '+FL_FL.OTCH_FL", out S, ref P);
                W.Add(S);
            }

            if (Where.ContainsKey("Name"))
            {
                String Name = Where["Name"].ToString();
                W.Add("FL_FL.FAM_FL+' '+FL_FL.NAME_FL+' '+FL_FL.OTCH_FL LIKE @Name");
                P["@Name"] = "%" + Name + "%";
            }
            con.Open();
            EGRLine E = new EGRLine();
            String WhereSQL = "";
            if (W.Count > 0) WhereSQL = " WHERE (" + String.Join(") AND (", W.ToArray()) + ")";
            String OrderBySQL = "";
            if (OrderBy.Count > 0) OrderBySQL = " ORDER BY " + String.Join(", ", OrderBy.ToArray());
            String SQL = @"
SELECT TOP 5 *,FL_FL.FAM_FL+' '+FL_FL.NAME_FL+' '+FL_FL.OTCH_FL AS NAMEP
FROM FL_IP
INNER JOIN FL_FL ON FL_FL.IDIP=FL_IP.IDIP
" + WhereSQL + OrderBySQL;
            SqlCommand cmd = new SqlCommand(SQL, con);
            foreach (KeyValuePair<String, Object> V in P) cmd.Parameters.AddWithValue(V.Key, V.Value);
            SqlDataReader r = cmd.ExecuteReader();
            //try                
            {
                Dictionary<String, Int32> Pole = new Dictionary<String, Int32>();
                for (Int32 i = 0; i < r.FieldCount; i++) Pole[r.GetName(i)] = i;
                while (r.Read())
                {
                    E.KontName = r.GetString(Pole["NAMEP"]);
                    E.INN = r.GetString(Pole["INN"]);
                    E.OGRN = r.GetString(Pole["OGRNIP"]);
                    E.GosReg = new DateTime(1970, 1, 1);
                    E.Region = "";
                    E.Raion = "";
                    E.Gorod = "";
                    E.Naspunkt = "";
                    E.Street = "";
                    E.Dom = "";
                    E.Korpus = "";
                    E.Office = "";
                    //if (!r.IsDBNull(Pole["Year"]) && !r.IsDBNull(Pole["Month"]) && !r.IsDBNull(Pole["Day"])) E.RegDate = new DateTime(r.GetInt32(Pole["Year"]), r.GetInt32(Pole["Month"]), r.GetInt32(Pole["Day"]));
                    EGRUL.Add(E);
                }
            }
            //finally
            { r.Close(); }
        }
        //catch{}
        //finally 
        { con.Close(); }
        return EGRUL;
    }    
  }
  
    public partial class Engine
    {
      private Dictionary<Int32, Kont> l_Kont;
      public Kont getKont(Int32 KontID)
      {
        if (l_Kont.ContainsKey(KontID)) return (Kont)l_Kont[KontID];
        Kont kont = new Kont(this, KontID);
        l_Kont[KontID] = kont;
        return kont;
      }
    }
    

}
